package com.ksj.Utils.JdbcTool;


import com.ksj.Config.RemoteConfigs;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.*;

public class JdbcTool {

    private static Connection conn;
    private static Statement st;

    private static Logger LOG = LoggerFactory.getLogger(JdbcTool.class);

    private static Map<String, Map<String, List<String>>> tableStruct;

    //初始化JDBC
    public static void init() {
        String url = "jdbc:mysql://" + RemoteConfigs.getInstance().mysql_host + ":"
                + RemoteConfigs.getInstance().mysql_port + "/"
                + RemoteConfigs.getInstance().mysql_database_name
                + "?useUnicode=true&characterEncoding=utf-8&autoReconnect=true";
        try {
            //启动驱动
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, RemoteConfigs.getInstance().mysql_username, RemoteConfigs.getInstance().mysql_password);
            st = conn.createStatement();
            tableStruct = null;
            tableStruct = new HashMap<>();
        } catch (Exception e) {
            LOG.error("数据库链接错误！链接URL为:" + url,e);
        }
    }

    //断开JDBC
    public static void stop(boolean isRestart) {
        if (st != null) {
            try {
                st.close();
            } catch (Exception e) {
                LOG.error("Statement关闭异常！",e);
            }
        }

        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
                LOG.error("Connection关闭异常！",e);
            }
        }

        st = null;
        conn = null;

        if (isRestart)
            init();
    }

    public static int insert(Map<String, Object> cols, String tableName) {
        //表结构缓存
        if (tableStruct.get(tableName) == null) {
            //查询表结构 并缓存起来

            ResultSet rs = null;
            try {
                rs = JdbcTool.st.executeQuery("desc " + tableName + ";");
                Map<String, List<String>> struct = new HashMap<>();
                struct.put("field", new ArrayList<>());
                struct.put("type", new ArrayList<>());
                while (rs.next()) {
                    struct.get("field").add(rs.getString("Field").toUpperCase());
                    struct.get("type").add(rs.getString("Type").toUpperCase());
                }
                tableStruct.put(tableName, struct);
                LOG.info("当前表结构缓存为" + tableStruct);
            } catch (SQLException e) {
                LOG.error("sql异常！",e);
            }


        }

        List<String> fields = tableStruct.get(tableName).get("field");
//        List<String> types = tableStruct.get(tableName).get("type");

        Map<String, Object> linkMap = new LinkedHashMap<>(cols);
        String sql = "INSERT INTO " + tableName + " (";

        Iterator it = linkMap.entrySet().iterator();
        List<String> keys = new ArrayList<>();
        List<Object> values = new ArrayList<>();
        while (it.hasNext()) {
            Map.Entry<String, Object> entity = (Map.Entry) it.next();
            if (fields.contains(entity.getKey().toUpperCase())) {
                keys.add(entity.getKey());
                values.add(entity.getValue());
            }
        }

        for (String key : keys) {
            sql += key + ",";
        }
        sql = sql.substring(0, sql.length() - 1);
        sql += ") values (";


        for (int i = 0; i < values.size(); i++) {
            Object value = values.get(i);
            sql += "'" + value + "',";
        }

        sql = sql.substring(0, sql.length() - 1);
        sql += ");";

        LOG.info(sql);


        int result = 0;
        try {
            result = st.executeUpdate(sql);
        } catch (SQLException e) {
            LOG.error("sql异常！",e);
        }
        return result;
    }

    public static List<Map<String, Object>> select(String[] cols, String tableName, String condition) {
        List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();

        String sql = "SELECT ";
        if (cols != null && cols.length > 0) {
            if (cols.length == 1 && cols[0].equals("*")) {
                sql += "*";
            } else {
                for (String col : cols) {
                    sql += col + ",";
                }
                sql = sql.substring(0, sql.length() - 1);
            }
        }

        sql += " FROM " + tableName;

        if (condition != null && condition.length() > 0) {
            sql += " WHERE " + condition;
        }

        sql += ";";
        LOG.info(sql);

        try {
            ResultSet rs = st.executeQuery(sql);
            ResultSetMetaData meta = rs.getMetaData();
            while (rs.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                for (int i = 1; i <= meta.getColumnCount(); i++) {
                    map.put(meta.getColumnName(i), rs.getObject(i));
                }
                result.add(map);
            }
        } catch (Exception e) {
            LOG.error("sql异常！",e);
        }

        return result;

    }

    public static Connection getConn() {
        return conn;
    }

    public static Statement getSt() {
        return st;

    }

    //修改报警状态！！
    public static String updateBaojing(String phoneVal, String leixing, String key, String leixingOld) {
        String sql = "update jiaoyi set alarmType ='" + leixing + "',status='9999'  where PHONE ='" + phoneVal + "'  and alarmName = '" + key + "' and alarmType = '" + leixingOld + "'";


        try {
            st.executeUpdate(sql);
        } catch (SQLException e) {
            LOG.error("sql异常！",e);
        }
        return "报警数据修改成功！！！";
    }


    //将报警数据插入jiaoyi表
    public static String insertBaojing(String PHONEVal, String type, String key, String alarmVal, String alarmEmerge) {
        UUID idx = UUID.randomUUID();
        String table[] = {"IDX", "PHONE", "status", "leixing", "beizhu"};
        Connection conn = null;
        PreparedStatement ps = null;
        String sql = "insert into jiaoyi(IDX, PHONE, status, flag, alarmName,alarmVal ,alarmType,alarmEmerge) values (?,?,?,?,?,?,?,?)";
        try {
            conn = JdbcTool.getConn();
            ps = conn.prepareStatement(sql);

            if (type.equals("14") == false) {
                ps.setString(1, idx.toString().replace("-", ""));
                ps.setString(2, PHONEVal);
                ps.setString(3, "0");
                ps.setString(4, "A");
                ps.setString(5, key);
                ps.setString(6, alarmVal);
                ps.setString(7, type);
                ps.setString(8, alarmEmerge);
            } else {
                ps.setString(1, idx.toString().replace("-", ""));
                ps.setString(2, PHONEVal);
                ps.setString(3, "9999");
                ps.setString(4, "A");
                ps.setString(5, key);
                ps.setString(6, alarmVal);
                ps.setString(7, type);
                ps.setString(8, alarmEmerge);
            }
            ps.executeUpdate();
            ps.close();
        } catch (SQLException e) {
            LOG.error("sql异常！",e);
        }
        return "插入数据库成功!!!";
    }

    @Override
    protected void finalize() throws Throwable {
        st.close();
        conn.close();
        super.finalize();
    }

}
